import sqlite3
from typing import List, Tuple
import logging
import csv

from pymongo import MongoClient
import aiofiles

from base.base_crawler import AbstractStore
from constant.model import NovelsInfo
from database.session import SessionLocal, init_db
from database.models import Novelsinfomodel # 导入你的 Novel 模型

logger = logging.getLogger(__name__)

class BQGStore(AbstractStore):
    store_path = "data/BQG"

    def __init__(self):
        pass

    def make_save_file_name(self, store_type: str) -> str:
        filename = f"{self.store_path}/{store_type}.csv"
        return filename
    
    async def save_data_to_csv(self, data: List[NovelsInfo], store_type: str) -> None:
        if not data:
            return
            
        filename = self.make_save_file_name(store_type)
        # 批量处理数据
        rows = [f"{novel.title},{novel.url},{novel.type}" for novel in data]
        content = "\n".join(rows) + "\n"
        
        # 检查文件是否存在，如果不存在则添加标题行
        try:
            async with aiofiles.open(filename, mode='r', encoding='utf-8') as f:
                pass
            file_exists = True
        except FileNotFoundError:
            file_exists = False
            
        async with aiofiles.open(filename, mode='a', encoding='utf-8') as f:
            if not file_exists:
                await f.write("title,url,type\n")
            await f.write(content)

    async def store_novels(self, novels_metadata: List[NovelsInfo]):
        await self.save_data_to_csv(novels_metadata, "novels")

    async def store_novel(self):
        pass

class BQGSqliteStore(AbstractStore):
    def __init__(self, db_path: str = "data/bqg.db"):
        self.db_path = db_path
        self.init_db()

    def init_db(self):
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            # Create novels table
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS novels (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    title TEXT NOT NULL,
                    url TEXT UNIQUE NOT NULL,
                    type TEXT
                )
            """)
            
            # Create chapters table
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS chapters (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    novel_id INTEGER,
                    title TEXT NOT NULL,
                    content TEXT,
                    FOREIGN KEY (novel_id) REFERENCES novels (id)
                )
            """)
            
            conn.commit()
            conn.close()
            logger.info("Database initialized successfully")
        except sqlite3.Error as e:
            logger.error(f"Error initializing database: {e}")

    def save_novels(self, novels: List[Tuple[str, str, str]]) -> None:
        if not novels:
            return
            
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            # 批量插入小说，忽略重复项
            cursor.executemany("""
                INSERT OR IGNORE INTO novels (title, url, type) 
                VALUES (?, ?, ?)
            """, novels)
            
            conn.commit()
            conn.close()
            logger.info(f"Saved {len(novels)} novels to database")
        except sqlite3.Error as e:
            logger.error(f"Error saving novels to database: {e}")

    def save_chapter_content(self, title: str, content: str) -> None:
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            # 插入章节内容
            cursor.execute("""
                INSERT INTO chapters (title, content) 
                VALUES (?, ?)
            """, (title, content))
            
            conn.commit()
            conn.close()
            logger.info(f"Saved chapter '{title}' to database")
        except sqlite3.Error as e:
            logger.error(f"Error saving chapter to database: {e}")


class BQGMongoStore(AbstractStore):
    def __init__(self):
        # 从 Settings 中安全地读取连接字符串
        from config.settings import Settings
        self.client = MongoClient(Settings.MONGO_CONNECTION_STRING)
        self.db = self.client['bqg_database']
        self.novels_collection = self.db['novels']
        """bqg_database
               novels
                   {"title": "", "type": "", "chapters": [{"count": int, "title": "", "content": ""}]}
        """
    def store_novels(self, novels: List[NovelsInfo]) -> None:
        # 这个方法在此场景下可以不实现，因为我们是按小说详情页来存储的
        pass
    def store_novel(self, metadata: dict, chapter: dict) -> None:
        try:
            # 插入章节内容
            
            self.novels_collection.update_one(
                {'title': metadata['title']},
                {'$push': {'chapters': chapter}}
            )
            logger.info(f"Saved novel '{metadata['title']}' with {chapter['title']} chapter to MongoDB")
        except Exception as e:
            logger.error(f"Error saving novel to MongoDB: {e}")

    def store_novel_metadata(self, metadata: dict) -> None:
        try:
            # 插入小说元数据
            self.novels_collection.insert_one(metadata)
            logger.info(f"Saved novel metadata '{metadata['title']}' to MongoDB")
        except Exception as e:
            logger.error(f"Error saving novel metadata to MongoDB: {e}")


class BQGMySQLStore(AbstractStore):
    
    def __init__(self):
        init_db()
        self.db = SessionLocal()

    async def store_novels(self, novelsinfo: List[NovelsInfo]) -> None:
        if not novelsinfo:
            return

        novels_to_add = []
        try:
            # 1. 从传入的小说信息中提取所有标题
            incoming_titles = {novel.title for novel in novelsinfo}

            # 2. 一次性查询数据库中已存在的所有相关小说标题
            existing_novels_query = self.db.query(Novelsinfomodel.title).filter(Novelsinfomodel.title.in_(incoming_titles))
            existing_titles = {title[0] for title in existing_novels_query.all()}

            # 3. 在内存中进行比较，找出需要新增的小说
            for novelinfo in novelsinfo:
                if novelinfo.title in existing_titles:
                    print(f"小说 '{novelinfo.title}' 已存在，跳过添加。")
                else:
                    print(f"准备添加小说: {novelinfo.title}")
                    novel = Novelsinfomodel(title=novelinfo.title, type=novelinfo.type, url=novelinfo.url)
                    novels_to_add.append(novel)
            
            # 4. 批量插入新小说
            if novels_to_add:
                self.db.add_all(novels_to_add)
                self.db.commit()
                print(f"成功添加 {len(novels_to_add)} 本新小说到数据库。")
            else:
                print("没有新小说需要添加到数据库。")

        except Exception as e:
            print(f"添加小说时出错: {e}")
            self.db.rollback()  # 出现异常时回滚事务
                

    async def store_novel(self, metadata: dict, chapter: dict) -> None:
        pass